package com.ichina.chat.dao.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;
import javax.persistence.UniqueConstraint;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.ichina.chat.dao.QuestionDao;
import com.ichina.chat.util.Page;
import com.ichina.chat.vo.Question;

@Component
@Transactional
public class QuestionDaoimpl implements QuestionDao {

	@Resource
	SessionFactory sessionFactory;
	private Session session = null;
	private Query query=null;
	private String hql="";
	private int count=0; 
	
	public void addQuestion(Question q) throws Exception
	{
		sessionFactory.getCurrentSession().save(q);
	}

	public void deleteQuestionByQid(int qid) throws Exception 
	{
		Question q =(Question) sessionFactory.getCurrentSession().load(Question.class,qid);
		sessionFactory.getCurrentSession().delete(q);
	}

	public void deleteQuestionByUser(String user) throws Exception {
		
		sessionFactory.getCurrentSession().delete(sessionFactory.getCurrentSession().createQuery("from Question where user="+user));
	}

	public void deleteQuestionByUserAndTypeid(String user, int typeid)
			throws Exception {

	}

	//根据时间与用户查询(带分页)
	@SuppressWarnings("unchecked")
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public List<Question> queryQuestioinByTime(Date time,String user, Page page)
			throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String st = sdf.format(time);
		hql="from Question where DATE_FORMAT(time,'%Y-%m-%d')=? and user =?";
		session=sessionFactory.getCurrentSession();
		query=session.createQuery(hql);
		query.setString(0,st);
		query.setString(1,user);
		query.setFirstResult(page.getPagecode() * page.getPagesize() - page.getPagesize());
		query.setMaxResults(page.getPagesize());
		return query.list();
	}

	//根据类型查询
	@SuppressWarnings("unchecked")
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public List<Question> queryQuestionByType(int typeid, Page page)
			throws Exception {
		hql="from Question where typeid="+typeid;
		session=sessionFactory.getCurrentSession();
		query=session.createQuery(hql);
		query.setFirstResult(page.getPagecode() * page.getPagesize() - page.getPagesize());
		query.setMaxResults(page.getPagesize());
		return query.list();
	}

	//根据用户与类型查询
	@SuppressWarnings("unchecked")
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public List<Question> queryQuestionByTypeAndUser(String username,
			int typeid, Page page) throws Exception {
		hql="from Question where typeid="+typeid+" and user="+username;
		session=sessionFactory.getCurrentSession();
		query=session.createQuery(hql);
		query.setFirstResult(page.getPagecode() * page.getPagesize() - page.getPagesize());
		query.setMaxResults(page.getPagesize());
		return query.list();
	}

	//根据关键字与用户名查询
	@SuppressWarnings("unchecked")
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public List<Question> queryQuestionByUser(String word, String username,
			Page page) throws Exception {
		hql="from Question where context like %"+word+"% and user="+username;
		session=sessionFactory.getCurrentSession();
		query=session.createQuery(hql);
		query.setFirstResult(page.getPagecode() * page.getPagesize() - page.getPagesize());
		query.setMaxResults(page.getPagesize());
		return query.list();
	}

	public void updateQuestion(Question q) throws Exception {
		
		sessionFactory.getCurrentSession().update(q);
	}
	
	//查询所有记录
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public int queryQuestionAll() throws Exception {
		hql="from Question";
		session=sessionFactory.getCurrentSession();
		count=session.createQuery(hql).list().size();
		return count;
	}

	//根据用户查找记录(带分页)
	@SuppressWarnings("unchecked")
	@Transactional(propagation=Propagation.NOT_SUPPORTED)
	public List<Question> queryRecondByUser(String user,Page page) throws Exception {
		hql="from Question where user=? order by time asc";
		session = sessionFactory.getCurrentSession();
		query = session.createQuery(hql);
		query.setString(0,user);
		query.setFirstResult(page.getPagecode() * page.getPagesize() - page.getPagesize());
		query.setMaxResults(page.getPagesize());
		return query.list();
	}
	
	/**
	 * 根据日期分组
	 * @return
	 * @throws Exception
	 */
	public List queryRecondByDate(){
		hql = "select time from Question group by DATE_FORMAT(time,'%Y-%m-%d')";
		session= sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		List time = query.list();
		List stringtime = new ArrayList();
		for (int i = 0; i < time.size(); i++) {
			stringtime.add(sdf.format(time.get(i)));
		}
		return stringtime;
	}

	public List queryRecondByUser(String user) throws Exception {
		//hql="new Question(q.context,q.user,q.time) from Question q where q.user=? order by q.time asc";
		hql="from Question where user=? order by time asc";
		session = sessionFactory.getCurrentSession();
		query = session.createQuery(hql);
		query.setString(0,user);
		List li = query.list();
		return li;
	}

	public List queryRecondByUser_date(String user, String date) throws Exception {
		//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//String st = sdf.format(date);
		hql="from Question where DATE_FORMAT(time,'%Y-%m-%d')=? and user =?";
		session=sessionFactory.getCurrentSession();
		query=session.createQuery(hql);
		query.setString(0,date);
		query.setString(1,user);
		return query.list();	
		}

}
